package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Bean.User;
import sql.JDBCUtil;

public class UserDao {
	/*
	 * selectByUsername(username : String) -> User
	 * 		通过用户名查询单一用户
	 * 		参数：
	 * 			username:用户名
	 * 		返回值类型：User
	 * 
	 * selectByUsernameAndPassword(username : String, password : String) -> User
	 * 		通过用户名和密码查询单一用户
	 * 		参数：
	 *			username:用户名
	 *			password:密码
	 *		返回值类型：User
	 *
	 * selectByUserId(user_id : String) -> User
	 *  	通过用户id查询单一用户
	 *  	参数：
	 *  		user_id:用户id
	 *  	返回值类型：User
	 *  
	 * selectAllUser() -> List<User>
	 * 		查询全部用户
	 * 		参数：
	 * 		返回值类型：List<User>
	 * 
	 * selectByCreateTime(createTime : String) -> List<User>
	 * 		通过注册日期查询用户群
	 * 		参数：
	 * 			createTime:注册日期
	 * 		返回值类型：List<User>
	 *
	 * selectUsersIsNotRoler() -> List<User>
	 *  	查询所有非管理员用户
	 *  	参数：
	 *  	返回值类型：List<User>
	 *  
	 * insert(user : User) -> None:
	 *		向数据库中插入新的用户
	 *		参数：
	 *			user:待插入用户对象
	 *		返回值类型：null
	 *
	 * dropByUsername(username : String) -> None
	 * 		删除指定用户名的用户
	 * 		参数：
	 * 			username:待删除用户名
	 * 		返回值类型：null
	 * 
	 * dropByUsernameAndPassword(username : String, password : String) -> None
	 * 		删除用户名和密码吻合的用户
	 * 		参数：
	 * 			username:用户名
	 * 			password:密码
	 * 		返回值类型：null
	 * 
	 * dropByUserId(user_id : String) -> None
	 * 		删除指定用户id的用户
	 * 		参数：
	 * 			user_id:用户Id
	 * 		返回值类型：null
	 * 
	 * updatePassword(username : String, password : String, newPassword : String) -> None
	 * 		更改用户名及密码吻合的指定用户的密码
	 * 		参数：
	 * 			username:用户名
	 * 			password:用户原密码
	 * 			newPassword:用户新密码
	 * 		返回值：null
	 */
	public User selectByUsername(String username) throws Exception {
		User user = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		Connection con = null;
		String sql = "select * from user where username = ?;";
		try {
			con = JDBCUtil.getcon();
			ps = con.prepareStatement(sql);
			ps.setString(1, username);
			rs = ps.executeQuery();
			if(rs.next()) {
				user = new User();
				// user.setUser_id(rs.getString("user_id"));
				user.setUsername(rs.getString("username"));
				user.setName("name");
				user.setPassword(rs.getString("password"));
				user.setCreateTime(rs.getString("createTime"));
				user.setRole(rs.getInt("role"));
			}
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(rs, ps, con);
		}
		return user;
	}
	public User selectByUsernameAndPassword(String username, String password) throws Exception {
		User user = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		Connection con = null;
		String sql = "select * from user where username = ? and password = ?";
		try {
			con = JDBCUtil.getcon();
			ps = con.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			rs = ps.executeQuery();
			if(rs.next()) {
				user = new User();
				//user.setUser_id(rs.getString("user_id"));
				user.setUsername(rs.getString("username"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setCreateTime(rs.getString("createTime"));
				user.setRole(rs.getInt("role"));
			}
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(rs, ps, con);
		}
		return user;
	}
	public User selectByUserId(String user_id) throws Exception {
		User user = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		Connection con = null;
		String sql = "select * from user where user_id = ?;";
		try {
			con = JDBCUtil.getcon();
			ps = con.prepareStatement(sql);
			ps.setString(1, user_id);
			rs = ps.executeQuery();
			if(rs.next()) {
				user = new User();
				//user.setUser_id(rs.getString("user_id"));
				user.setUsername(rs.getString("username"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setCreateTime(rs.getString("createTime"));
				user.setRole(rs.getInt("role"));
			}
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(rs, ps, con);
		}
		return user;
	}
	public List<User> selectAllUser() throws Exception {
		List<User> list = new ArrayList<User>();
		User user = null;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from user";
		try {
			con = JDBCUtil.getcon();
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				user = new User();
				//user.setUser_id(rs.getString("user_id"));
				user.setUsername(rs.getString("username"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setCreateTime(rs.getString("createTime"));
				user.setRole(rs.getInt("role"));
				list.add(user);
			}
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(rs, ps, con);
		}
		return list;
	}
	public List<User> selectByCreateTime(String createTime) throws Exception {
		List<User> list = new ArrayList<User>();
		User user = null;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from user where DATE(createTime) = ?;";
		try {
			con = JDBCUtil.getcon();
			ps = con.prepareStatement(sql);
			ps.setString(1, createTime);
			rs = ps.executeQuery();
			while(rs.next()) {
				user = new User();
				//user.setUser_id(rs.getString("user_id"));
				user.setUsername(rs.getString("username"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setCreateTime(rs.getString("createTime"));
				user.setRole(rs.getInt("role"));
				list.add(user);
			}
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(rs, ps, con);
		}
		return list;
	}
	public List<User> selectUsersIsNotRoler() throws Exception {
		List<User> list = new ArrayList<User>();
		User user = null;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from user where role = 0";
		try {
			con = JDBCUtil.getcon();
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				user = new User();
				//user.setUser_id(rs.getString("user_id"));
				user.setUsername(rs.getString("username"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setCreateTime(rs.getString("createTime"));
				user.setRole(rs.getInt("role"));
				list.add(user);
			}
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(rs, ps, con);
		}
		return list;
	}
	public void insert(User user) throws Exception {
		Connection con = null;
		PreparedStatement ps = null;
		String sql = "insert into user value(?, ?, ?, null, 0)";
		try {
			con = JDBCUtil.getcon();
			ps = con.prepareStatement(sql);
			//ps.setString(1, user.getUser_id());
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getName());
			ps.setString(3, user.getPassword());
			// ps.setString(3, user.getCreateTime());
			ps.executeUpdate();
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(null, ps, con);
		}
	}
	public void dropByUsername(String username) throws Exception {
		Connection con = null;
		PreparedStatement ps = null;
		String sql = "delete from user where username = ?";
		try {
			con = JDBCUtil.getcon();
			ps = con.prepareStatement(sql);
			ps.setString(1, username);
			ps.executeUpdate();
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(null, ps, con);
		}
	}
	public void dropByUsernameAndPassword(String username, String password) throws Exception {
		Connection con = null;
		PreparedStatement ps = null;
		String sql = "delete from user where username = ? and password = ?";
		try {
			con = JDBCUtil.getcon();
			ps = con.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			ps.executeUpdate();
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(null, ps, con);
		}
	}
//	public void dropByUserId(String user_id) throws Exception {
//		Connection con = null;
//		PreparedStatement ps = null;
//		String sql = "delete from user where username = ?";
//		try {
//			con = JDBCUtil.getcon();
//			ps = con.prepareStatement(sql);
//			ps.setString(1, username);
//			ps.executeUpdate();
//		} catch(SQLException e) {
//			throw new RuntimeException(e);
//		} finally {
//			JDBCUtil.close(null, ps, con);
//		}
//	}
	public void updatePassword(String username, String password, String newPassword) throws Exception {
		Connection con = null;
		PreparedStatement ps = null;
		String sql = "update user set password = ? where username = ? and password = ?";
		try {
			con = JDBCUtil.getcon();
			ps = con.prepareStatement(sql);
			ps.setString(1, newPassword);
			ps.setString(2, username);
			ps.setString(3, password);
			ps.executeUpdate();
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(null, ps, con);
		}
	}
}
